/**
 * Copyright 2005 SIB Framework

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
package org.sibframework.sda.reflect;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.sibframework.sda.SdaFactory;
import org.sibframework.sda.SimpleDataAccess;
import org.sibframework.sda.dbmodel.Column;
import org.sibframework.sda.dbmodel.Table;


public class SqlServerReflect implements IDBReflect {

	@Override
	public List<String> showSchemas() throws Exception {
		String sql = "select * from sys.schemas";
		return SdaFactory.getInstance().queryStrings(sql);
	}

	@Override
	public List<Table> showTables(String schema) throws Exception {

		String sql1 = "select schema_id from sys.schemas where name = '" + schema + "'";
		String sid = SdaFactory.getInstance().queryString(sql1);
		
		String sql = "select a.name name, cast(ISNULL(b.value, '') as varchar(1000)) comments from sys.tables a LEFT OUTER JOIN sys.extended_properties b on a.object_id = b.major_id and b.minor_id = 0";
		

		if (schema != null && schema.length() > 0) {
			sql += " and a.schema_id = " + sid;
		}
		sql += " order by a.name";
		
		List<Map<String, Object>> ls = SdaFactory.getInstance().query2Map(sql);
		 List<Table> result = new ArrayList<Table>();
		for (Map<String, Object> map : ls) {
			Table table = new Table();
			table.setTableName((String) map.get("name"));
			table.setDescription((String) map.get("comments"));
			result.add(table);
		}
		return result;
	}
	
	@Override
	public List<Table> showTables() throws Exception {
		return this.showTables(null);
	}

	@Override
	public List<Column> showColumns(String tableName) throws Exception {
		
		Map<String, String> javaTypes = SdaFactory.getInstance().getColumnJavaType(tableName);
		

		String sql1 = "select object_id from sys.tables where name = '" + tableName + "'";
		String tid = SdaFactory.getInstance().queryString(sql1);
		
		String sql =  "select a.*, b.name data_type, cast(ISNULL(c.value, '') as varchar(1000)) comments "
				    + "from sys.columns a left JOIN sys.types b on a.user_type_id = b.user_type_id "
				    + "left outer join sys.extended_properties c on a.object_id = c.major_id and a.column_id = c.minor_id "
					+ "where object_id = " + tid;
		//get fields from table
		List<Map<String, Object>> cos = SdaFactory.getInstance().query2Map(sql);
		//List<String> pks = SdaFactory.getInstance().queryStrings("select col.column_name from user_constraints con,  user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = upper('" + tableName + "')");
		/*
		 * 3����ѯĳ�����е�����ֶ���ơ������ñ�����Ӧ���ֶ���
			select distinct(col.column_name),r.table_name,r.column_name 
			from 
			user_constraints con,
			user_cons_columns col, 
			(select t2.table_name,t2.column_name,t1.r_constraint_name 
			from user_constraints t1,user_cons_columns t2 
			where t1.r_constraint_name=t2.constraint_name 
			and t1.table_name='YourTableName'
			) r 
			where con.constraint_name=col.constraint_name 
			and con.r_constraint_name=r.r_constraint_name 
			and con.table_name='YourTableName'
		 */
		List<Column> columns = new ArrayList<Column>();
		//fields declare.
		for (Map<String, Object> map : cos) {
			Column col = new Column();
			col.setName((String) map.get("name"));
			col.setType((String) map.get("dataType"));
			col.setComments((String) map.get("comments"));
			if (col.getComments() == null) {
				col.setComments("");
			}
			try {
				
				//isNullable
				try {
					col.setNullable((Boolean)map.get("isNullable"));
				} catch (Exception e) {
					e.printStackTrace();
				}
				
				//max_length
				try {
					col.setLength((BigDecimal) map.get("max_length"));
				} catch (Exception e) {
					e.printStackTrace();
				}
				
				//precision
				if (map.get("precision") != null) {
					col.setPrecision(((BigDecimal)map.get("precision")).intValue());
				}
				
				//scale
				if (map.get("scale") != null) {
					col.setScale(((BigDecimal)map.get("scale")).intValue());
				}
				
				
			} catch (Exception e) {
			}
			col.setJavaType(javaTypes.get(col.getName()));

			columns.add(col);
		}
		return columns;
	}

	@Override
	public List<Table> showTableColumns(String schema) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

}
